from flask import Flask, render_template, request, jsonify, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func
import pandas as pd
from surprise import Dataset, Reader, KNNBasic
from surprise.model_selection import train_test_split
import random
from datetime import datetime, timedelta




app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@up01:3306/ecommerce_search_tickets?charset=utf8mb4'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy()
db.init_app(app)




# 数据库模型
class User(db.Model):
    __tablename__ = 'users'
    user_id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), nullable=False)
    email = db.Column(db.String(100), nullable=False, unique=True)
    created_at = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())




class Product(db.Model):
    __tablename__ = 'products'
    product_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text)
    category = db.Column(db.String(100))
    price = db.Column(db.Numeric(10, 2))
    created_at = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())




class SearchLog(db.Model):
    __tablename__ = 'search_logs'
    log_id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
    query = db.Column(db.String(255), nullable=False)
    search_time = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())
    has_results = db.Column(db.Boolean)




class Ticket(db.Model):
    __tablename__ = 'tickets'
    ticket_id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
    query = db.Column(db.String(255))
    issue_type = db.Column(db.Enum('no_results', 'irrelevant', 'sorting', 'other'), nullable=False)
    description = db.Column(db.Text, nullable=False)
    status = db.Column(db.Enum('open', 'in_progress', 'resolved', 'closed'), server_default='open')
    created_at = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())
    resolved_at = db.Column(db.TIMESTAMP)




class UserProductInteraction(db.Model):
    __tablename__ = 'user_product_interactions'
    interaction_id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
    product_id = db.Column(db.Integer, db.ForeignKey('products.product_id'))
    interaction_type = db.Column(db.Enum('view', 'click', 'purchase'), nullable=False)
    interaction_time = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())




# 初始化数据库
@app.before_first_request
def init_db():
    db.create_all()
    seed_database()



def seed_database():
    """填充模拟数据"""
    if User.query.count() == 0:
        # 添加用户
        users = [
            User(username='user1', email='user1@example.com'),
            User(username='user2', email='user2@example.com'),
            User(username='user3', email='user3@example.com'),
            User(username='admin', email='admin@example.com')
        ]
        db.session.add_all(users)
        db.session.commit()

        # 添加商品
        categories = ['electronics', 'clothing', 'books', 'home']
        products = []
        for i in range(1, 101):
            products.append(Product(
                name=f'Product {i}',
                description=f'Description for product {i}',
                category=random.choice(categories),
                price=round(random.uniform(10, 500), 2)
            ))
        db.session.add_all(products)
        db.session.commit()

        # 添加搜索日志和交互数据
        users = User.query.all()
        products = Product.query.all()

        for user in users:
            # 搜索日志
            for _ in range(random.randint(5, 20)):
                query = random.choice(['phone', 'laptop', 'shirt', 'book', 'furniture', 'watch'])
                db.session.add(SearchLog(
                    user_id=user.user_id,
                    query=query,
                    has_results=random.choice([True, False])
                ))

            # 用户-商品交互
            for _ in range(random.randint(10, 30)):
                db.session.add(UserProductInteraction(
                    user_id=user.user_id,
                    product_id=random.choice(products).product_id,
                    interaction_type=random.choice(['view', 'click', 'purchase'])
                ))

        db.session.commit()




# 协同过滤推荐
def get_collaborative_filtering_recommendations(user_id, n=5):
    """获取基于用户的协同过滤推荐"""
    # 从数据库加载交互数据
    interactions = UserProductInteraction.query.all()
    data = []
    for interaction in interactions:
        data.append({
            'user_id': interaction.user_id,
            'product_id': interaction.product_id,
            'rating': 1 if interaction.interaction_type == 'view' else
            3 if interaction.interaction_type == 'click' else 5
        })

    if not data:
        return []

    df = pd.DataFrame(data)
    reader = Reader(rating_scale=(1, 5))
    data = Dataset.load_from_df(df[['user_id', 'product_id', 'rating']], reader)

    # 训练测试分割
    trainset, testset = train_test_split(data, test_size=0.2)

    # 使用KNNBasic算法
    sim_options = {
        'name': 'cosine',
        'user_based': True  # 计算用户相似度
    }
    algo = KNNBasic(sim_options=sim_options)
    algo.fit(trainset)

    # 获取用户未交互过的商品
    all_products = {p.product_id for p in Product.query.all()}
    user_products = {i.product_id for i in UserProductInteraction.query.filter_by(user_id=user_id).all()}
    unseen_products = list(all_products - user_products)

    # 预测评分
    predictions = []
    for product_id in unseen_products:
        pred = algo.predict(user_id, product_id)
        predictions.append((product_id, pred.est))

    # 按预测评分排序
    predictions.sort(key=lambda x: x[1], reverse=True)

    # 返回前n个推荐商品
    recommended_ids = [p[0] for p in predictions[:n]]
    recommended_products = Product.query.filter(Product.product_id.in_(recommended_ids)).all()

    return recommended_products

# 路由
@app.route('/')
def home():
    return render_template('index.html')


@app.route('/search', methods=['GET'])
def search():
    query = request.args.get('q', '')
    user_id = 1  # 模拟登录用户
    if query:
        # 记录搜索日志
        has_results = True  # 模拟有结果
        db.session.add(SearchLog(
            user_id=user_id,
            query=query,
            has_results=has_results
        ))
        db.session.commit()

        # 获取搜索结果 (这里模拟搜索结果)
        products = Product.query.filter(
            (Product.name.contains(query)) |
            (Product.description.contains(query))
        ).limit(20).all()

        # 获取推荐商品
        recommendations = get_collaborative_filtering_recommendations(user_id)

        return render_template(
            'search_results.html',
            query=query,
            products=products,
            recommendations=recommendations
        )
    return redirect(url_for('home'))
if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)